const { query, run } = require('../database/db');

// 获取所有商品
async function getAllProducts(category = null, minPrice = null, maxPrice = null) {
    let sql = 'SELECT * FROM products WHERE 1=1';
    const params = [];

    if (category) {
        sql += ' AND category = ?';
        params.push(category);
    }
    if (minPrice !== null) {
        sql += ' AND price >= ?';
        params.push(minPrice);
    }
    if (maxPrice !== null) {
        sql += ' AND price <= ?';
        params.push(maxPrice);
    }

    sql += ' ORDER BY created_at DESC';
    return await query(sql, params);
}

// 获取商品详情
async function getProductById(id) {
    const products = await query('SELECT * FROM products WHERE id = ?', [id]);
    return products[0];
}

// 添加商品
async function addProduct(productData) {
    const { name, description, price, stock, category, image_url } = productData;
    const result = await run(
        'INSERT INTO products (name, description, price, stock, category, image_url) VALUES (?, ?, ?, ?, ?, ?)',
        [name, description, price, stock, category, image_url]
    );
    return result.lastID;
}

// 更新商品
async function updateProduct(id, productData) {
    const { name, description, price, stock, category, image_url } = productData;
    await run(
        'UPDATE products SET name = ?, description = ?, price = ?, stock = ?, category = ?, image_url = ? WHERE id = ?',
        [name, description, price, stock, category, image_url, id]
    );
}

// 删除商品
async function deleteProduct(id) {
    await run('DELETE FROM products WHERE id = ?', [id]);
}

// 更新商品库存
async function updateStock(id, quantity) {
    await run('UPDATE products SET stock = stock - ? WHERE id = ?', [quantity, id]);
}

// 获取商品分类
async function getCategories() {
    const result = await query('SELECT DISTINCT category FROM products');
    return result.map(item => item.category);
}

module.exports = {
    getAllProducts,
    getProductById,
    addProduct,
    updateProduct,
    deleteProduct,
    updateStock,
    getCategories
}; 